package com.smartaccounting.dao;

import com.smartaccounting.model.Income;
import com.smartaccounting.util.DatabaseUtil;

import java.math.BigDecimal;
import java.sql.*;
import java.time.LocalDate;
import java.util.ArrayList;
import java.util.List;

public class IncomeDAO {

    // 根据ID获取收入记录
    public Income getIncomeById(int incomeId) throws SQLException {
        String sql = "SELECT * FROM income WHERE income_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, incomeId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                return extractIncomeFromResultSet(rs);
            }
        }
        return null;
    }

    // 获取用户的所有收入记录
    public List<Income> getIncomesByUserId(int userId) throws SQLException {
        List<Income> incomes = new ArrayList<>();
        String sql = "SELECT * FROM income WHERE user_id = ? ORDER BY income_date DESC";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                incomes.add(extractIncomeFromResultSet(rs));
            }
        }
        return incomes;
    }

    // 获取用户指定时间范围内的收入记录
    public List<Income> getIncomesByUserIdAndDateRange(int userId, LocalDate startDate, LocalDate endDate) throws SQLException {
        List<Income> incomes = new ArrayList<>();
        String sql = "SELECT * FROM income WHERE user_id = ? AND income_date BETWEEN ? AND ? ORDER BY income_date DESC";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setDate(2, Date.valueOf(startDate));
            stmt.setDate(3, Date.valueOf(endDate));
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                incomes.add(extractIncomeFromResultSet(rs));
            }
        }
        return incomes;
    }

    // 获取用户指定月份的收入记录
    public List<Income> getIncomesByUserIdAndMonth(int userId, String monthYear) throws SQLException {
        List<Income> incomes = new ArrayList<>();
        String sql = "SELECT * FROM income WHERE user_id = ? AND DATE_FORMAT(income_date, '%Y-%m') = ? ORDER BY income_date DESC";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setString(2, monthYear);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                incomes.add(extractIncomeFromResultSet(rs));
            }
        }
        return incomes;
    }

    // 添加收入记录
    public boolean addIncome(Income income) throws SQLException {
        String sql = "INSERT INTO income (user_id, amount, category, source, income_date, description) VALUES (?, ?, ?, ?, ?, ?)";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, income.getUserId());
            stmt.setBigDecimal(2, income.getAmount());
            stmt.setString(3, income.getCategory());
            stmt.setString(4, income.getSource());
            stmt.setDate(5, Date.valueOf(income.getIncomeDate()));
            stmt.setString(6, income.getDescription());
            return stmt.executeUpdate() > 0;
        }
    }

    // 更新收入记录
    public boolean updateIncome(Income income) throws SQLException {
        String sql = "UPDATE income SET amount=?, category=?, source=?, income_date=?, description=? WHERE income_id=?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setBigDecimal(1, income.getAmount());
            stmt.setString(2, income.getCategory());
            stmt.setString(3, income.getSource());
            stmt.setDate(4, Date.valueOf(income.getIncomeDate()));
            stmt.setString(5, income.getDescription());
            stmt.setInt(6, income.getIncomeId());
            return stmt.executeUpdate() > 0;
        }
    }

    // 删除收入记录
    public boolean deleteIncome(int incomeId) throws SQLException {
        String sql = "DELETE FROM income WHERE income_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, incomeId);
            return stmt.executeUpdate() > 0;
        }
    }

    // 获取用户总收入金额
    public BigDecimal getTotalIncomeByUserId(int userId) throws SQLException {
        String sql = "SELECT SUM(amount) FROM income WHERE user_id = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                BigDecimal total = rs.getBigDecimal(1);
                return total != null ? total : BigDecimal.ZERO;
            }
        }
        return BigDecimal.ZERO;
    }

    // 获取用户指定月份的总收入
    public BigDecimal getTotalIncomeByUserIdAndMonth(int userId, String monthYear) throws SQLException {
        String sql = "SELECT SUM(amount) FROM income WHERE user_id = ? AND DATE_FORMAT(income_date, '%Y-%m') = ?";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setString(2, monthYear);
            ResultSet rs = stmt.executeQuery();
            if (rs.next()) {
                BigDecimal total = rs.getBigDecimal(1);
                return total != null ? total : BigDecimal.ZERO;
            }
        }
        return BigDecimal.ZERO;
    }

    // 获取收入分类统计
    public List<Object[]> getIncomeCategorySummary(int userId, String monthYear) throws SQLException {
        List<Object[]> summary = new ArrayList<>();
        String sql = "SELECT category, SUM(amount) as total FROM income WHERE user_id = ? AND DATE_FORMAT(income_date, '%Y-%m') = ? GROUP BY category";
        try (Connection conn = DatabaseUtil.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            stmt.setString(2, monthYear);
            ResultSet rs = stmt.executeQuery();
            while (rs.next()) {
                Object[] row = new Object[2];
                row[0] = rs.getString("category");
                row[1] = rs.getBigDecimal("total");
                summary.add(row);
            }
        }
        return summary;
    }

    // 从结果集提取收入对象
    private Income extractIncomeFromResultSet(ResultSet rs) throws SQLException {
        Income income = new Income();
        income.setIncomeId(rs.getInt("income_id"));
        income.setUserId(rs.getInt("user_id"));
        income.setAmount(rs.getBigDecimal("amount"));
        income.setCategory(rs.getString("category"));
        income.setSource(rs.getString("source"));
        income.setIncomeDate(rs.getDate("income_date").toLocalDate());
        income.setDescription(rs.getString("description"));
        income.setCreatedAt(rs.getTimestamp("created_at").toLocalDateTime());
        return income;
    }
}